//package wmmd.chat;
package chatManagers;

import java.sql.*;
import java.util.Date;
import db.DatabaseConnector;

public class DBMSManager{
    private static DBMSManager dbm = null;     // Using the singleton design pattern
    private DatabaseConnector conn;
    
    public DBMSManager() {
        conn = new DatabaseConnector(DatabaseConnector.getDatabaseType());;
    }
    
    // Singleton design pattern
    public static DBMSManager getInstance() {
        if(dbm == null) {
            dbm = new DBMSManager();
        }
        return dbm;
    }
    
    public int AddPost(int chatid, int userid, String comment) {
        Object[] values = new Object[] {chatid, userid, -1, 1, comment.replace('\'', '`')};
        conn.freeformSQL("INSERT INTO chatPosts (chatid, senderID, recipID, delivered, message) "
                + "VALUES (?,?,?,?,?)", values);
        
        return -1;
    }
    
    // Function that inserts a private message into the private table
    public int AddPvtMessage(int chatid, int senderid, int recipid, String comment) {
        Object values[] = new Object[] {chatid, senderid, recipid, comment.replace('\'', '`')};
        conn.freeformSQL("INSERT INTO chatPosts (chatID, senderID, recipID, message)"
                + "VALUES (?,?,?,?)", values);
        return -1;
    }
    
    public String[] getGroups(String uName) {
        String groups[] = {""};
        ResultSet rs;
        rs = conn.selectSQL_RS("SELECT role FROM USER_ROLES WHERE username = ?", new Object[]{uName});
        try {
            while(rs.next()) {
                int i = 0;
                groups[i] = rs.getString("role");
                i++;
            }
        } catch(SQLException se) {
            se.printStackTrace();
        }
        return groups;
    }
    
    public int checkLogin(String uName) {
        ResultSet rs;
        ResultSet rs2;
        int validID = 0;
        boolean isChatUser = false;
        
        rs = conn.selectSQL_RS("SELECT * FROM USER_ROLES WHERE username = ? AND role = 'chat'", new Object[]{uName});
        try {
            if(rs != null) {
                while(rs.next())
                    isChatUser = true;
                rs.close();
            }
        } catch(SQLException se) {
            se.printStackTrace();
        }
        
        // If the person is part of the chat group, get their numeric userID for chats
        if(isChatUser) {
            rs2 = conn.selectSQL_RS("SELECT ID FROM chatUsers WHERE username = ? AND removed = 0", new Object[]{uName});
            try {
                if(rs2 != null) {
                    while(rs2.next())
                        validID = rs2.getInt("ID");
                    rs2.close();
                }
            } catch(SQLException se) {
                se.printStackTrace();
            }
        }
        return validID;
    }
    
    public int setUserChat(int uid, int cid) {
        int result;
        int status = 0;
        
        result = conn.freeformSQL("UPDATE chatUsers SET chatID = ? WHERE ID = ?", new Object[]{cid, uid});
        
        if(result != PreparedStatement.EXECUTE_FAILED)
            status = cid;
        
        return status;
    }
    
    public ResultSet getMembers(int cid) {
        ResultSet rs;
        
        if(cid > 0)
            rs = conn.selectSQL_RS("SELECT ID, username FROM chatUsers WHERE removed = 0 AND chatID = ?", new Object[]{cid});
        else
            rs = conn.selectSQL_RS("SELECT ID, username FROM chatUsers WHERE removed = 0");
        
        return rs;
    }
    
    public void addMembers(String username) {
        conn.freeformSQL("INSERT INTO chatUsers (username) VALUES (?)", new Object[]{username});
    }
    
    public void removeMembers(String username) {
        conn.freeformSQL("UPDATE chatUsers SET removed = 1 WHERE username = ?", new Object[]{username});
    }
    
    public int unsetUserChat(int uid) {
        int result;
        int status = 1;
        
        result = conn.freeformSQL("UPDATE chatUsers SET chatID = 0 WHERE ID = ?", new Object[]{uid});
        
        if(result != PreparedStatement.EXECUTE_FAILED)
            status = 0;
        
        return status;
    }
    
    public ResultSet getAllPosts(int chatid, int recipID, int limit) {
        ResultSet rs;
        int offset = 0;
        
        // Get the ID of the last post
        int lastPostID = this.getLastPost(chatid);
        
        // If we're limiting the retrieved posts to a certain number
        // (i.e., limit > 0), set the offset accordingly.
        if (limit > 0) {
            offset = lastPostID - limit;
        }
        //Do we want public or ALL posts?
        if(recipID < 0)
            rs = conn.selectSQL_RS("SELECT chatUsers.username AS uName, chatPosts.postTime AS mtime, " +
                    "chatPosts.message AS message, chatPosts.ID AS mid, chatPosts.recipID as rID " +
                    "FROM chatUsers " +
                    "RIGHT JOIN chatPosts " +
                    "ON chatUsers.ID = chatPosts.senderID " +
                    "WHERE chatPosts.chatID = ? AND chatPosts.recipID = ? " +
                    "AND chatPosts.ID > ? " +
                    "ORDER BY chatPosts.ID ASC", new Object[]{chatid, recipID, offset});
        else
            rs = conn.selectSQL_RS("SELECT chatUsers.username AS uName, chatPosts.postTime AS mtime, " +
                    "chatPosts.message AS message, chatPosts.ID AS mid, chatPosts.recipID as rID " +
                    "FROM chatUsers " +
                    "RIGHT JOIN chatPosts " +
                    "ON chatUsers.ID = chatPosts.senderID " +
                    "WHERE chatPosts.chatID = ? AND chatPosts.delivered = 1 " +
                    "AND chatPosts.ID > ? " +
                    "ORDER BY chatPosts.ID ASC", new Object[]{chatid, offset});
        return rs;
    }
    
    public ResultSet getLatestPosts(int chatid, int lmid) {
        ResultSet rs;
        
        rs = conn.selectSQL_RS("SELECT chatUsers.username AS uName, chatPosts.postTime AS mtime, " +
                "chatPosts.message AS message, chatPosts.ID AS mid " +
                "FROM chatUsers " +
                "RIGHT JOIN chatPosts " +
                "ON chatUsers.ID = chatPosts.senderID " +
                "WHERE chatPosts.chatID = ? " +
                "AND chatPosts.ID > ? " +
                "AND chatPosts.recipID = -1 " +
                "ORDER BY chatPosts.ID ASC", new Object[]{chatid, lmid});
        return rs;
    }
    
    public int getLastPost(int chatid) {
        int maxid = 0;
        ResultSet rs;
        
        rs = conn.selectSQL_RS("SELECT MAX(ID) AS maxid " +
                "FROM chatPosts " +
                "WHERE chatPosts.chatID = ? " +
                "AND chatPosts.recipID = -1", new Object[]{chatid});
        try {
            while(rs.next()) {
                maxid = rs.getInt("maxid");
            }
            rs.close();
        } catch (SQLException se) {
            se.printStackTrace();
        }
        
        return maxid;
    }
    
    // Function that returns all undelivered private messages for the given user in the
    // given chat.  After returning the private messages, the function marks the
    // messages as delivered
    public ResultSet getPrivateMessages(int chatid, int uid) {
        ResultSet rs;
        
        rs = conn.selectSQL_RS("SELECT chatUsers.username AS uName, chatPosts.postTime AS mtime, " +
                "chatPosts.message AS message, chatPosts.ID AS mid " +
                "FROM chatUsers " +
                "RIGHT JOIN chatPosts " +
                "ON chatUsers.ID = chatPosts.senderID " +
                "WHERE chatPosts.chatID = ? " +
                "AND chatPosts.recipID = ? " +
                "AND chatPosts.delivered = 0 " +
                "ORDER BY chatPosts.ID ASC", new Object[]{chatid, uid});
        
        conn.freeformSQL("UPDATE chatPosts " +
                "SET delivered = 1 " +
                "WHERE chatID = ? " +
                "AND recipID = ?", new Object[]{chatid, uid} );
        
        return rs;
    }
    
    public int addNewChat(String name, String desc, int chatRestrict, String chatType,
            String authString, int owner) {
        int status = 0;
        ResultSet rs;
        
        String currDate = getDateTime();
        
        conn.freeformSQL("INSERT INTO chatRooms (name, description, owner, chatRestrict, chatType, users, " +
                "startDate, endDate) " +
                "VALUES (?,?,?,?,?,?,?,?)", new Object[]{name, desc, owner, chatRestrict, chatType, authString, currDate, currDate});
        
        rs = conn.selectSQL_RS("SELECT ID FROM chatRooms WHERE name = ?", new Object[]{name});
        
        try {
            if(rs != null)
                while(rs.next())
                    status = Integer.parseInt(rs.getString("ID"));
            rs.close();
        } catch (SQLException se) {
            se.printStackTrace();
        }
        
        return status;
    }
    
    // function to determine if a chat is still active; called in chatloader
    public boolean isChatActive(int chatid) {
        ResultSet rs;
        Date now = new Date();
        
        String chatType = "";
        boolean status = false;
        long startDate = 0;
        long endDate = 0;
        
        rs = conn.selectSQL_RS("SELECT startDate, endDate, chatType FROM chatRooms WHERE ID = ?", new Object[]{chatid});
        
        try {
            while(rs.next()) {
                startDate = rs.getTimestamp("startDate").getTime();
                endDate = rs.getTimestamp("endDate").getTime();
                chatType = rs.getString("chatType");
            }
            rs.close();
            
            // If the chat is less than 24 hours old OR if the chat is not
            // an "expert" chat, then the chat should stay active.
            if(startDate == endDate) {
                if (((startDate + (24*60*60*1000) <= now.getTime())) && (chatType.equals("expert"))) {
                    this.EndChat(chatid);
                } else {
                    status = true;
                }
            }
        } catch(SQLException se) {
            se.printStackTrace();
        }
        
        return status;
    }
    
    // function to determine if a chat is still active; called in chatloader
    public boolean isExpertChat(int chatid) {
        ResultSet rs;
        boolean status = false;
        String chatType = "";
        
        rs = conn.selectSQL_RS("SELECT chatType FROM chatRooms WHERE ID = ?", new Object[]{chatid});
        
        try {
            while(rs.next())
                chatType = rs.getString("chatType");
            rs.close();
            
            if(chatType.equals("expert"))
                status = true;
        } catch (SQLException se) {
            se.printStackTrace();
        }
        return status;
    }
    
    // function that terminates a chat; sets the end date to the current time
    public int EndChat(int chatid) {
        int result;
        int status = 1;
        Date now = new Date();
        java.text.SimpleDateFormat df = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        
        result = conn.freeformSQL("UPDATE chatRooms SET endDate = ? WHERE ID = ?", new Object[]{df.format(now), chatid});
        
        if(result != PreparedStatement.EXECUTE_FAILED)
            status = 0;
        
        return status;
    }
    
    // function that returns the owner of the given chat
    public String getOwner(int cid) {
        ResultSet rs;
        String owner = "";
        
        rs = conn.selectSQL_RS("SELECT owner FROM chatRooms WHERE ID = ?", new Object[]{cid});
        
        try {
            if(rs != null) {
                while(rs.next())
                    owner = rs.getString("owner");
                rs.close();
            }
        } catch (SQLException se) {
            se.printStackTrace();
        }
        
        return owner;
    }
    
    // function that returns the name of the given chat
    public Timestamp getStartDate(int cid) {
        ResultSet rs;
        Timestamp t = null;
        
        rs = conn.selectSQL_RS("SELECT startDate FROM chatRooms WHERE ID = ?", new Object[]{cid});
        
        try {
            if(rs != null) {
                while(rs.next())
                    t = rs.getTimestamp("startDate");
                rs.close();
            }
        } catch (SQLException se) {
            se.printStackTrace();
        }
        
        return t;
    }
    
    // function that returns the name of the given chat
    public String getTitle(int cid) {
        ResultSet rs;
        String cName = "";
        
        rs = conn.selectSQL_RS("SELECT name FROM chatRooms WHERE ID = ?", new Object[]{cid});
        
        try {
            if(rs != null) {
                while(rs.next())
                    cName = rs.getString("name");
                rs.close();
            }
        } catch (SQLException se) {
            se.printStackTrace();
        }
        
        return cName;
    }
    
    // For the admin section - returns all chats
    // Vincent:  not sure this is going to work...
    public ResultSet getAllChats() {
        ResultSet rs;
        
        rs = conn.selectSQL_RS("SELECT chatUsers.username AS owner, " +
                "chatRooms.ID AS id, chatRooms.name AS name, " +
                "chatRooms.description AS description, " +
                "chatRooms.startDate AS startDate, chatRooms.endDate AS endDate " +
                "FROM chatUsers " +
                "RIGHT JOIN chatRooms " +
                "ON chatUsers.ID = chatRooms.owner");
        
        return rs;
    }
    
    // For the chat client - returns all open chats based on chat type
    public ResultSet getAllOpenChats(String chatType) {
        ResultSet rs;
        
        if(chatType.equals("all"))
            rs = conn.selectSQL_RS("SELECT chatUsers.username AS owner, " +
                    "chatRooms.ID AS id, chatRooms.name AS name, "+
                    "chatRooms.description AS description, " +
                    "chatRooms.users AS users " +
                    "FROM chatUsers " +
                    "RIGHT JOIN chatRooms " +
                    "ON chatUsers.ID = chatRooms.owner " +
                    "WHERE chatRooms.startDate = chatRooms.endDate", new Object[0]);
        else
            rs = conn.selectSQL_RS("SELECT chatUsers.username AS owner, " +
                    "chatRooms.ID AS id, chatRooms.name AS name, "+
                    "chatRooms.description AS description " +
                    "chatRooms.users AS users " +
                    "FROM chatUsers " +
                    "RIGHT JOIN chatRooms " +
                    "ON chatUsers.ID = chatRooms.owner " +
                    "WHERE chatRooms.startDate = chatRooms.endDate " +
                    "AND chatRooms.chatType = ?", new Object[]{chatType});
        
        return rs;
    }
    
    private String getDateTime() {
        java.text.DateFormat dateFormat = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        Date date = new Date();
        return dateFormat.format(date);
    }
    
    public void closeConnection() {
        if(conn != null) conn.CleanUp();
    }
}